using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

namespace ProcessesSMS
{
	/// <summary>
	/// Summary description for CKVN.
	/// </summary>
	public class CKVN
	{
		
		private string connString = ConfigurationSettings.AppSettings["STOCK_CONNECTION_STRING_STOCKS"].ToString();
		private SMS_MO WMO;
		private SMS_MT WMT;
		//private SMS_LIST WML;
		ArrayList arr = new ArrayList();
		public CKVN(SMS_MO MO)
		{
			WMO = MO;
		}

		public CKVN()
		{
		}		
		public void run()
		{
			WMT = new SMS_MT(WMO);
			string tmpMsg = WMO.Message.Substring(WMO.CommandCode.Length).Trim().Replace("  ", " ").Replace("  ", " ");
			tmpMsg = tmpMsg.Replace("  ", " ").Trim().ToUpper();
			string[] msg = tmpMsg.Split(new char[] { ' ' });
			string strReturnMsg = GetReturnMsg(msg);						
			if(strReturnMsg!="")
			{
				WMT.AddMessage(WMO.UserID, strReturnMsg, SMS_ContentType.SMS_TEXT);
				WMT.MsgType = SMS_MsgType.SMS_TINHTIEN;
				WMT.Insert(); 
			}					
			return;
		}
		private string GetReturnMsg(string[] arr)
		{	
			string msgBody=string.Empty;
			if(WMO.ServiceID.Replace("04","")=="6272")
			{
				#region 5GTGD1,5GTGD2,5KLGD1,5KLGD2,5TANG1,5TANG2,5GIAM1,5GIAM2,5NNMUA1,5NNMUA2,5NNBAN2,MACK,MACK1-MACK2...
				if(arr.Length==1)
				{
					if(arr[1]=="5GTGD1")
					{
						return GET5GTGD1();						
					}
					else if(arr[1]=="5GTGD2")
					{
						return GET5GTGD2();						
					}
					else if(arr[1]=="5KLGD1")
					{
						return GET5KLGD1();						
					}
					else if(arr[1]=="5KLGD2")
					{
						return GET5KLGD2();						
					}
					else if(arr[1]=="5TANG1")
					{
						return GET5TANG1();						
					}
					else if(arr[1]=="5TANG2")
					{
						return GET5TANG2();						
					}
					else if(arr[1]=="5GIAM1")
					{
						return GET5GIAM1();						
					}
					else if(arr[1]=="5GIAM2")
					{
						return GET5GIAM2();						
					}
					else if(arr[1]=="5NNMUA1")
					{
						return GET5NNMUA1();						
					}
					else if(arr[1]=="5NNMUA2")
					{
						return GET5NNMUA2();						
					}			
					else if(arr[1]=="5NNBAN2")
					{
						return GET5NNBAN2();						
					}
					else
					{					
						string[] subarr = arr[1].Split(',');
						if(subarr.Length==1)//neu la xem mot ma CK
						{
							return GETMACK(arr[1]);							
						}
						else
						{
							return GETMACK(subarr);							
						}					
					}
				}
				#endregion				
			}
			else if(WMO.ServiceID.Replace("04","")=="6472")
			{
				#region MACK NGAYTHANG
				if(arr.Length==3)
				{
					string d = arr[2].Substring(0,2) + "/" + arr[2].Substring(2,2) + "/" + DateTime.Now.Year;
					string mack = arr[1].ToUpper();					
					return GETMACK(mack,d);
				}
				#endregion
			}
			return ConfigSettings.ReadSetting("SMS_HELP_CKVN");
		}
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN MACK
		public string GETMACK(string MACK)
		{
			#region SQL systax 
			string strSQl ="begin";
			strSQl +=" declare @intHN int";
			strSQl +=" declare @controlCode nchar(1)";
			strSQl +=" declare @symbol nchar(6)";
			strSQl +=" set @symbol='"+MACK+"'";
			strSQl +=" set @controlCode =(select top 1 controlCode from HCM_Market_Stat order by HCMDate desc)";
			strSQl +=" set @intHN = (select count(STOCK_ID) from STS_STOCKS_INFO where CODE = @symbol)";
			strSQl +=" if(@intHN>0)";
			strSQl +=" begin";
			strSQl +=" select CODE as MA,TRADING_DATE as DATE,CEILING_PRICE as [TRAN],FLOOR_PRICE as SAN,BASIC_PRICE as TC,MATCH_PRICE as GIAKL,TOTAL_TRADING_QTTY as KLKL";
			strSQl +=" from STS_STOCKS_INFO where CODE=@symbol";
			strSQl +=" end";
			strSQl +=" else";
			strSQl +=" begin";
			strSQl +=" select StockSymbol as MA,ModifiedDate as DATE,[Ceiling] as [TRAN],[Floor] as SAN,PriorClosePrice as TC,case when(@controlCode='O') then Last else ProjectOpen end as GIAKL,LastVol as KLKL";
			strSQl +=" from HCM_Security where StockSymbol = @symbol";
			strSQl +=" end";
			strSQl +=" end";
			#endregion				
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{
				string MA=ds.Tables["gets"].Rows[0]["MA"].ToString().Replace(" ","");
				string d=DateTime.Parse(ds.Tables["gets"].Rows[0]["DATE"].ToString()).ToString("dd/MM/yy");
				decimal TRAN =decimal.Parse(ds.Tables["gets"].Rows[0]["TRAN"].ToString())/1000;
				decimal SAN = decimal.Parse(ds.Tables["gets"].Rows[0]["SAN"].ToString())/1000;
				decimal TC= decimal.Parse(ds.Tables["gets"].Rows[0]["TC"].ToString())/1000;
				decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[0]["GIAKL"].ToString())/1000;
				decimal CHANGE=(decimal.Parse(ds.Tables["gets"].Rows[0]["GIAKL"].ToString())- decimal.Parse(ds.Tables["gets"].Rows[0]["TC"].ToString()))/1000;
				strResult +=MA + " " + d + "\n";
				strResult +="TRAN/SAN/TC:"+TRAN.ToString().Replace('.',',') + "/" + SAN.ToString().Replace('.',',') + "/" + TC.ToString().Replace('.',',') + "\n";
				strResult +="GIA:" + GIAKL.ToString().Replace('.',',') + " (";
				if(CHANGE>0) strResult +="+";
				strResult +=CHANGE.ToString().Replace('.',',') +"%)\n";
				strResult +="TONG KLUONG:" + ds.Tables["gets"].Rows[0]["KLKL"].ToString() + " cp";
			}
			ds.Dispose();
			return strResult;
		}		
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN MACK NGAY THANG
		public string GETMACK(string MACK,string date)
		{
			#region SQL systax 
			string strSQl ="begin";
			strSQl +=" declare @intHN int";
			strSQl +=" declare @controlCode nchar(1)";
			strSQl +=" declare @symbol nchar(6)";
			strSQl +=" declare @date  nvarchar(10)";
			strSQl +=" set @symbol='"+MACK+"'";
			strSQl +=" set @controlCode =(select top 1 controlCode from HCM_Market_Stat order by HCMDate desc)";
			strSQl +=" set @intHN = (select count(STOCK_ID) from STS_STOCKS_INFO where CODE = @symbol)";
			strSQl +=" set @date='"+date+"'";
			strSQl +=" if(@intHN>0)";
			strSQl +=" begin";
			strSQl +=" select CODE as MA,TRADING_DATE as DATE,CEILING_PRICE as [TRAN],FLOOR_PRICE as SAN,BASIC_PRICE as TC,MATCH_PRICE as GIAKL,TOTAL_TRADING_QTTY as KLKL";
			strSQl +=" from STS_STOCKS_INFO_HISTORY where CODE=@symbol and convert(nvarchar(10),TRADING_DATE,103)=@date";
			strSQl +=" end";
			strSQl +=" else";
			strSQl +=" begin";
			strSQl +=" select StockSymbol as MA,ModifiedDate as DATE,[Ceiling] as [TRAN],[Floor] as SAN,PriorClosePrice as TC,case when(@controlCode='O') then Last else ProjectOpen end as GIAKL,LastVol as KLKL";
			strSQl +=" from HCM_Security_History where StockSymbol = @symbol and convert(nvarchar(10),ModifiedDate,103)=@date";
			strSQl +=" end";
			strSQl +=" end";
			#endregion				
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{
				string MA=ds.Tables["gets"].Rows[0]["MA"].ToString().Replace(" ","");
				string d=DateTime.Parse(ds.Tables["gets"].Rows[0]["DATE"].ToString()).ToString("dd/MM/yy");
				decimal TRAN =decimal.Parse(ds.Tables["gets"].Rows[0]["TRAN"].ToString())/1000;
				decimal SAN = decimal.Parse(ds.Tables["gets"].Rows[0]["SAN"].ToString())/1000;
				decimal TC= decimal.Parse(ds.Tables["gets"].Rows[0]["TC"].ToString())/1000;
				decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[0]["GIAKL"].ToString())/1000;
				decimal CHANGE=(decimal.Parse(ds.Tables["gets"].Rows[0]["GIAKL"].ToString())- decimal.Parse(ds.Tables["gets"].Rows[0]["TC"].ToString()))/1000;
				strResult +=MA + " " + d + "\n";
				strResult +="TRAN/SAN/TC:"+TRAN.ToString().Replace('.',',') + "/" + SAN.ToString().Replace('.',',') + "/" + TC.ToString().Replace('.',',') + "\n";
				strResult +="GIA:" + GIAKL.ToString().Replace('.',',') + " (";
				if(CHANGE>0) strResult +="+";
				strResult +=CHANGE.ToString().Replace('.',',') +"%)\n";
				strResult +="TONG KLUONG:" + ds.Tables["gets"].Rows[0]["KLKL"].ToString() + " cp";
			}
			else
			{
				strResult="Ngay "  + date + " khong co giao dich";
			}
			ds.Dispose();
			return strResult;
		}		
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN MACK CK1,CK2,...
		public string GETMACK(string[] MACK)
		{		
			string strResult=string.Empty;			
			for(int i=0;i<MACK.Length;i++)
			{
				strResult +=GetShortMsg(MACK[i].Trim().ToUpper());
			}
			return strResult;
		}		
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5GTGD1
		public string GET5GTGD1()
		{
			string strSQl =" begin";
			strSQl +=" declare @controlCode nchar(1)";
			strSQl +=" set @controlCode =(select top 1 controlCode from HCM_Market_Stat order by HCMDate desc)";
			strSQl +=" select top 5 StockSymbol as MA,";
			strSQl +=" case when(@controlCode='O') then Last else ProjectOpen end as GIAKL,LastVal  as GTGD";
			strSQl +=" from HCM_Security order by LastVal desc";
			strSQl +=" end";
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA=ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;		
					decimal GTGD = Math.Round(decimal.Parse(ds.Tables["gets"].Rows[i]["GTGD"].ToString())/1000000000,3);
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  " + GTGD.ToString().Replace('.',',') + " ty\n";	
				}
			}
			ds.Dispose();
			return strResult;
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5GTGD2
		public string GET5GTGD2()
		{
			string strSQl =" select top 5 CODE as MA,MATCH_PRICE as GIAKL,TOTAL_TRADING_VALUE as GTGD from STS_STOCKS_INFO order by TOTAL_TRADING_VALUE desc";			
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA=ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;		
					decimal GTGD = Math.Round(decimal.Parse(ds.Tables["gets"].Rows[i]["GTGD"].ToString())/1000000000,3);
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  " + GTGD.ToString().Replace('.',',') + " ty\n";						
				}
			}
			ds.Dispose();
			return strResult;
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5KLGD1
		public string GET5KLGD1()
		{
			string strSQL=" begin";
			strSQL +=" declare @controlCode nchar(1)";
			strSQL +=" set @controlCode =(select top 1 controlCode from HCM_Market_Stat order by HCMDate desc)";
			strSQL +=" select top 5 StockSymbol as MA,";
			strSQL +=" case";
			strSQL +=" when(@controlCode='O') then Last else ProjectOpen end as GIAKL";
			strSQL +=" ,LastVol  as KLGD";
			strSQL +=" from HCM_Security order by LastVol desc";
			strSQL +=" end";
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQL,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA=ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;						
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  " + ds.Tables["gets"].Rows[i]["KLGD"].ToString()+ " cp\n";	
				}
			}
			ds.Dispose();
			return strResult;
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5KLGD2
		public string GET5KLGD2()
		{
			string strSQl =" select top 5 CODE as MA,MATCH_PRICE as GIAKL,TOTAL_TRADING_QTTY as KLGD from STS_STOCKS_INFO order by TOTAL_TRADING_QTTY desc";			
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{					
					string MA=ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;						
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  " + ds.Tables["gets"].Rows[i]["KLGD"].ToString()+ " cp\n";	
				}
			}
			ds.Dispose();
			return strResult;
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5TANG1
		public string GET5TANG1()
		{
			#region SQL systax			
			string strSQL="begin ";
			strSQL +=" declare @controlCode nchar(1)";
			strSQL +=" set @controlCode =(select top 1 controlCode from HCM_Market_Stat order by HCMDate desc)";
			strSQL +=" if(@controlCode='O')";
			strSQL +=" begin";
			strSQL +=" select top 5 StockSymbol as MA,Last as GIAKL,(Last-PriorClosePrice) as CHANGE,((Last-PriorClosePrice)/PriorClosePrice)*100 as PCT_CHANGE";
			strSQL +=" from HCM_Security where (Last-PriorClosePrice)>0 and (PriorClosePrice>0)";
			strSQL +=" order by (Last-PriorClosePrice) desc";
			strSQL +=" end";
			strSQL +=" else";
			strSQL +=" begin";
			strSQL +=" select top 5 StockSymbol as MA,ProjectOpen as GIAKL,(ProjectOpen -PriorClosePrice) as CHANGE,((ProjectOpen -PriorClosePrice)/PriorClosePrice)*100 as PCT_CHANGE	";
			strSQL +=" from HCM_Security where (ProjectOpen -PriorClosePrice)>0 and (PriorClosePrice>0)";
			strSQL +=" order by (ProjectOpen -PriorClosePrice) desc	";
			strSQL +=" end";
			strSQL +=" end";
			#endregion
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQL,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA = ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;
					decimal CHANGE = decimal.Parse(ds.Tables["gets"].Rows[i]["CHANGE"].ToString())/1000;
					decimal PCT_CHANGE =Math.Round(decimal.Parse(ds.Tables["gets"].Rows[i]["PCT_CHANGE"].ToString()),1);			
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  +" + CHANGE.ToString().Replace('.',',') + " (+" + PCT_CHANGE.ToString().Replace('.',',') + "%)\n";
				}
			}
			ds.Dispose();
			return strResult;
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5TANG2
		public string GET5TANG2()
		{				
			string strSQL="select top 5 CODE as MA,MATCH_PRICE as GIAKL,(MATCH_PRICE-BASIC_PRICE) as CHANGE,((MATCH_PRICE-BASIC_PRICE)/BASIC_PRICE)*100 as PCT_CHANGE";
			strSQL  +=" from STS_STOCKS_INFO where BASIC_PRICE>0 and (MATCH_PRICE-BASIC_PRICE)>0";
			strSQL +=" order by (MATCH_PRICE-BASIC_PRICE) desc";
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQL,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA = ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;
					decimal CHANGE = decimal.Parse(ds.Tables["gets"].Rows[i]["CHANGE"].ToString())/1000;
					decimal PCT_CHANGE =Math.Round(decimal.Parse(ds.Tables["gets"].Rows[i]["PCT_CHANGE"].ToString()),1);			
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  +" + CHANGE.ToString().Replace('.',',') + " (+" + PCT_CHANGE.ToString().Replace('.',',') + "%)\n";
				}
			}
			ds.Dispose();
			return strResult;
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5GIAM1
		public string GET5GIAM1()
		{
			#region SQL systax			
			string strSQL="begin ";
			strSQL +=" declare @controlCode nchar(1)";
			strSQL +=" set @controlCode =(select top 1 controlCode from HCM_Market_Stat order by HCMDate desc)";
			strSQL +=" if(@controlCode='O')";
			strSQL +=" begin";
			strSQL +=" select top 5 StockSymbol as MA,Last as GIAKL,(Last-PriorClosePrice) as CHANGE,((Last-PriorClosePrice)/PriorClosePrice)*100 as PCT_CHANGE";
			strSQL +=" from HCM_Security where PriorClosePrice>0 and Last>0 and (Last-PriorClosePrice)<0";
			strSQL +=" order by (Last-PriorClosePrice) asc";
			strSQL +=" end";
			strSQL +=" else";
			strSQL +=" begin	";
			strSQL +=" select top 5 StockSymbol as MA,ProjectOpen as GIAKL,(ProjectOpen -PriorClosePrice) as CHANGE,((ProjectOpen -PriorClosePrice)/PriorClosePrice)*100 as PCT_CHANGE	";
			strSQL +=" from HCM_Security where PriorClosePrice>0 and ProjectOpen>0 and (ProjectOpen -PriorClosePrice)<0";
			strSQL +=" order by (ProjectOpen -PriorClosePrice) asc";
			strSQL +=" end";
			strSQL +=" end";
			#endregion
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQL,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{				
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA = ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;
					decimal CHANGE = decimal.Parse(ds.Tables["gets"].Rows[i]["CHANGE"].ToString())/1000;
					decimal PCT_CHANGE =Math.Round(decimal.Parse(ds.Tables["gets"].Rows[i]["PCT_CHANGE"].ToString()),1);			
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  " + CHANGE.ToString().Replace('.',',') + " (" + PCT_CHANGE.ToString().Replace('.',',') + "%)\n";
				}
			}
			ds.Dispose();
			return strResult;
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5GIAM2
		public string GET5GIAM2()
		{
				
			string strSQL="select top 5 CODE as MA,MATCH_PRICE as GIAKL,(MATCH_PRICE-BASIC_PRICE) as CHANGE,((MATCH_PRICE-BASIC_PRICE)/BASIC_PRICE)*100 as PCT_CHANGE";
			strSQL  +=" from STS_STOCKS_INFO where BASIC_PRICE>0 and MATCH_PRICE>0 and (MATCH_PRICE-BASIC_PRICE)<0";
			strSQL +=" order by (MATCH_PRICE-BASIC_PRICE) asc";
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQL,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{				
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA = ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");				
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;
					decimal CHANGE = decimal.Parse(ds.Tables["gets"].Rows[i]["CHANGE"].ToString())/1000;			
					decimal PCT_CHANGE =Math.Round(decimal.Parse(ds.Tables["gets"].Rows[i]["PCT_CHANGE"].ToString()),1);
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  " +  CHANGE.ToString().Replace('.',',') + " (" + PCT_CHANGE.ToString().Replace('.',',') + "%)\n";
				}
			}
			ds.Dispose();
			return strResult;
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5NNMUA1
		public string GET5NNMUA1()
		{
			string strSQL="begin";
			strSQL +=" declare @controlCode nchar(1)";
			strSQL +=" set @controlCode =(select top 1 controlCode from HCM_Market_Stat order by HCMDate desc)";
			strSQL +=" select top 5 HCM_Security.StockSymbol as MA,";
			strSQL +=" case when(@controlCode='O') then HCM_Security.Last else HCM_Security.ProjectOpen end as GIAKL,";
			strSQL +=" HCM_FRoom.BuyQtty as NNMUA";
			strSQL +=" from HCM_Security,HCM_FRoom where HCM_FRoom.BuyQtty>0 and HCM_FRoom.StockNo =HCM_Security.StockNo order by HCM_FRoom.BuyQtty desc";
			strSQL +=" end";
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQL,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{				
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA=ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;						
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  " + ds.Tables["gets"].Rows[i]["NNMUA"].ToString()+ " cp\n";			
				}
			}
			ds.Dispose();
			return strResult;	
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5NNMUA2
		public string GET5NNMUA2()
		{
			string strSQL="select top 5 CODE as MA,MATCH_PRICE as GIAKL,BUY_FOREIGN_QTTY as NNMUA";
			strSQL  +=" from STS_STOCKS_INFO where BUY_FOREIGN_QTTY>0 ";
			strSQL +=" order by BUY_FOREIGN_QTTY desc";
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQL,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{				
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA=ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;						
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  " + ds.Tables["gets"].Rows[i]["NNMUA"].ToString()+ " cp\n";				
				}
			}
			ds.Dispose();
			return strResult;	
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5NNBAN1....Khong co du lieu
		
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region CKVN 5NNBAN2
		public string GET5NNBAN2()
		{
			string strSQL="select top 5 CODE as MA,MATCH_PRICE as GIAKL,SELL_FOREIGN_QTTY as NNBAN";
			strSQL  +=" from STS_STOCKS_INFO where SELL_FOREIGN_QTTY>0";
			strSQL +=" order by SELL_FOREIGN_QTTY desc";
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQL,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{				
				for(int i=0;i<ds.Tables["gets"].Rows.Count;i++)
				{
					string MA=ds.Tables["gets"].Rows[i]["MA"].ToString().Replace(" ","");
					decimal GIAKL = decimal.Parse(ds.Tables["gets"].Rows[i]["GIAKL"].ToString())/1000;					
					strResult +=MA + "  " + GIAKL.ToString().Replace('.',',') + "  " + ds.Tables["gets"].Rows[i]["NNBAN"].ToString()+ " cp\n";
				}
			}
			ds.Dispose();
			return strResult;	
		}
		#endregion
		/*-------------------------------------------------------------------------------------------------*/
		#region Get Short Return
		private string GetShortMsg(string MACK)
		{
			#region SQL systax 
			string strSQl ="begin";
			strSQl +=" declare @intHN int";
			strSQl +=" declare @controlCode nchar(1)";
			strSQl +=" declare @symbol nchar(6)";
			strSQl +=" set @symbol='"+MACK+"'";
			strSQl +=" set @controlCode =(select top 1 controlCode from HCM_Market_Stat order by HCMDate desc)";
			strSQl +=" set @intHN = (select count(STOCK_ID) from STS_STOCKS_INFO where CODE = @symbol)";
			strSQl +=" if(@intHN>0)";
			strSQl +=" begin";
			strSQl +=" select CODE as MA,TRADING_DATE as DATE,BASIC_PRICE as TC,MATCH_PRICE as GIAKL";
			strSQl +=" from STS_STOCKS_INFO where CODE=@symbol";
			strSQl +=" end";
			strSQl +=" else";
			strSQl +=" begin";
			strSQl +=" select StockSymbol as MA,ModifiedDate as DATE,PriorClosePrice as TC,case when(@controlCode='O') then Last else ProjectOpen end as GIAKL";
			strSQl +=" from HCM_Security where StockSymbol = @symbol";
			strSQl +=" end";
			strSQl +=" end";
			#endregion				
			string strResult=string.Empty;
			SqlConnection oledbConn = new SqlConnection(connString);
			DataSet ds = new DataSet();						
			oledbConn.Open();			
			SqlDataAdapter oleda = new SqlDataAdapter(strSQl,oledbConn);
			oleda.Fill(ds,"gets");
			oledbConn.Close();
			if(ds.Tables["gets"].Rows.Count>0)
			{				
				string MA=ds.Tables["gets"].Rows[0]["MA"].ToString().Replace(" ","");
				decimal GIAKL =decimal.Parse(ds.Tables["gets"].Rows[0]["GIAKL"].ToString())/1000;
				decimal CHANGE =(decimal.Parse(ds.Tables["gets"].Rows[0]["GIAKL"].ToString()) - decimal.Parse(ds.Tables["gets"].Rows[0]["TC"].ToString()))/1000;
				strResult +=MA + ":" + GIAKL.ToString().Replace('.',',') + " (";
				if(CHANGE>0) strResult +="+";
				strResult += CHANGE.ToString().Replace('.',',') + "%)\n";				
			}
			ds.Dispose();
			return strResult;
		}
		#endregion
		/*-------------------------------------------------------*/
	}
}
